package dto;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import model.ProducerOwnCategory;

public class DtoProducerOwnCategory {
	private static List<ProducerOwnCategory> getAll(String sql,
			DataManager dataManager) {
		List<ProducerOwnCategory> producerOwnCategories = new ArrayList<ProducerOwnCategory>();
		ProducerOwnCategory p = null;
		Connection con = dataManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				p = new ProducerOwnCategory();
				p.setProducerOwnCategoryId(rs.getInt("producerOwnCategoryId"));
				p.setCategoryId(rs.getInt("categoryId"));
				p.setProducerId(rs.getInt("producerId"));
				producerOwnCategories.add(p);
			}
		} catch (SQLException e) {
			// TODO: handle exception
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (st != null)
					st.close();
				dataManager.closeConnection(con);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return producerOwnCategories;
	}

	public static List<ProducerOwnCategory> getAll(DataManager dataManager) {
		String sql = "select * from producerowncategory";
		return getAll(sql, dataManager);
	}

	public static List<ProducerOwnCategory> getByCategoryId(int categoryId,
			DataManager dataManager) {
		String sql = "select * from producerowncategory where categoryId ="
				+ categoryId;
		return getAll(sql, dataManager);
	}

	public static ProducerOwnCategory getProducerOwnCategory(
			DataManager dataManager, int producerOwnCategoryId) {
		String sql = "select * from ProducerOwnCategory where producerOwnCategoryId="
				+ producerOwnCategoryId;
		List<ProducerOwnCategory> list = getAll(sql, dataManager);

		if (list.isEmpty())
			return null;
		return list.get(0);
	}
	
	public static int getNewProducerOwnCategoryId(DataManager dataManager) {
		String sql = "select * from producerowncategory ORDER BY producerOwnCategoryId DESC limit 1";
		List<ProducerOwnCategory> list = getAll(sql, dataManager);

		if (list.isEmpty())
			return 0;
		return list.get(0).getProducerOwnCategoryId();
	}

	public static int isHaveProducerOwnCategory(DataManager dataManager,
			int producerId, int categoryId) {
		String sqlQuery = "select * from ProducerOwnCategory where producerId = "
				+ producerId + " and categoryId = " + categoryId;

		List<ProducerOwnCategory> list = getAll(sqlQuery, dataManager);

		if (list.isEmpty())
			return 0;
		
		return list.get(0).getProducerOwnCategoryId();
	}

	public static void executeNonQuery(DataManager dataManager, String sqlQuery) {
		Connection con = dataManager.getConnection();
		Statement st = null;
		try {
			st = con.createStatement();
			st.executeUpdate(sqlQuery);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (st != null)
					st.close();
				dataManager.closeConnection(con);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	public static void addProducerOwnCategory(DataManager dataManager, int producerId, int categoryId) {
		String sqlQuery = "insert into ProducerOwnCategory (producerId, categoryId) values (" + producerId + 
						", " + categoryId +")";
		executeNonQuery(dataManager, sqlQuery);
	}
}
